package faceattendsystem.daoImpl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import faceattendsystem.dao.IErrorsheetDao;
import faceattendsystem.entity.Employee;
import faceattendsystem.entity.Errorsheet;
import faceattendsystem.entity.Mechanism;
import faceattendsystem.entity.PageBean;
import faceattendsystem.util.JDBCTemplate;
import faceattendsystem.util.JDBCTemplate.PreparedStatementSetter;
import faceattendsystem.util.JDBCTemplate.ResultSetCallBack;



public class ErrorsheetDaoImpl implements IErrorsheetDao {

	@Override
	public PageBean<Errorsheet> query(Errorsheet errorsheet, String bdate, String edate, int pc, int ps) {
		PageBean<Errorsheet> pb = new PageBean<Errorsheet>();
		pb.setPc(pc);
		pb.setPs(ps);

		String mechid = errorsheet.getMechanism().getMechid();
		String empid = errorsheet.getEmployee().getEmpid();
		String tjtype = errorsheet.getTjtype();

		String sql = "select count(*) from errorsheet,employee where errorsheet.empid=employee.empid and employee.mechid like '%"+mechid+"%' and employee.empid like '%"+empid+"%' and tjtype like '%"+tjtype+"%' and tjdate>=? and tjdate<=? and employee.state=0";

		Number num = JDBCTemplate.singleQuery(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setString(1, bdate);
				pstmt.setString(2, edate);
			}
		}, new ResultSetCallBack<Number>() {
			@Override
			public Number processRs(ResultSet rs) throws SQLException {
				return rs.getInt(1);
			}
		});

		int tr = 1;
		if (num != null) {
			tr = num.intValue();
		}
		pb.setTr(tr);

		sql = "select tjdate,employee.mechid,employee.empid,errtype,tjtype,tjmsg from errorsheet,employee where errorsheet.empid=employee.empid and employee.mechid like '%"+mechid+"%' and employee.empid like '%"+empid+"%' and tjtype like '%"+tjtype+"%' and tjdate>=? and tjdate<=? and employee.state=0 order by tjdate desc,employee.mechid,employee.empid asc limit ?,?";

		List<Errorsheet> errorsheetList = JDBCTemplate.query(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setString(1, bdate);
				pstmt.setString(2, edate);
				pstmt.setInt(3, (pc - 1) * ps);
				pstmt.setInt(4, ps);
			}
		}, new ResultSetCallBack<Errorsheet>() {
			@Override
			public Errorsheet processRs(ResultSet rs) throws SQLException {
				Errorsheet errorsheet = new Errorsheet();
				errorsheet.setTjdate(rs.getDate(1));
				Mechanism mechanism = new Mechanism();
				mechanism.setMechid(rs.getString(2));
				errorsheet.setMechanism(mechanism);
				Employee employee = new Employee();
				employee.setEmpid(rs.getString(3));
				errorsheet.setEmployee(employee);
				errorsheet.setErrtype(rs.getString(4));
				errorsheet.setTjtype(rs.getString(5));
				errorsheet.setTjmsg(rs.getString(6));
				return errorsheet;
			}
		});

		pb.setBeanList(errorsheetList);
		return pb;
	}

	@Override
	public int add(Errorsheet errorsheet) {
		String sql = "insert into errorsheet values(?,?,?,?,?,?)";
		
		int result = JDBCTemplate.update(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setDate(1, errorsheet.getTjdate());
				pstmt.setString(2, errorsheet.getMechanism().getMechid());
				pstmt.setString(3, errorsheet.getEmployee().getEmpid());
				pstmt.setString(4, errorsheet.getErrtype());
				pstmt.setString(5, errorsheet.getTjtype());
				pstmt.setString(6, errorsheet.getTjmsg());
			}
		});
		
		return result;
	}

	@Override
	public List<Errorsheet> queryAll() {
		String sql = "select * from errorsheet";
		
		List<Errorsheet> errorsheetList = JDBCTemplate.query(sql,null, new ResultSetCallBack<Errorsheet>() {
			@Override
			public Errorsheet processRs(ResultSet rs) throws SQLException {
				Errorsheet errorsheet = new Errorsheet();
				errorsheet.setTjdate(rs.getDate(1));
				Mechanism mechanism = new Mechanism();
				mechanism.setMechid(rs.getString(2));
				errorsheet.setMechanism(mechanism);
				Employee employee = new Employee();
				employee.setEmpid(rs.getString(3));
				errorsheet.setEmployee(employee);
				errorsheet.setErrtype(rs.getString(4));
				errorsheet.setTjtype(rs.getString(5));
				errorsheet.setTjmsg(rs.getString(6));
				return errorsheet;
			}
		});
		
		return errorsheetList;
	}

	@Override
	public PageBean<Errorsheet> queryAll(Errorsheet errorsheet, String bdate, String edate) {
		PageBean<Errorsheet> pb = new PageBean<Errorsheet>();

		String mechid = errorsheet.getMechanism().getMechid();
		String empid = errorsheet.getEmployee().getEmpid();
		String tjtype = errorsheet.getTjtype();

		String sql = "select tjdate,employee.mechid,employee.empid,errtype,tjtype,tjmsg from errorsheet,employee where errorsheet.empid=employee.empid and employee.mechid like '%"+mechid+"%' and employee.empid like '%"+empid+"%' and tjtype like '%"+tjtype+"%' and tjdate>=? and tjdate<=? and employee.state=0 order by tjdate desc,employee.mechid,employee.empid asc";

		List<Errorsheet> errorsheetList = JDBCTemplate.query(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setString(1, bdate);
				pstmt.setString(2, edate);
			}
		}, new ResultSetCallBack<Errorsheet>() {
			@Override
			public Errorsheet processRs(ResultSet rs) throws SQLException {
				Errorsheet errorsheet = new Errorsheet();
				errorsheet.setTjdate(rs.getDate(1));
				Mechanism mechanism = new Mechanism();
				mechanism.setMechid(rs.getString(2));
				errorsheet.setMechanism(mechanism);
				Employee employee = new Employee();
				employee.setEmpid(rs.getString(3));
				errorsheet.setEmployee(employee);
				errorsheet.setErrtype(rs.getString(4));
				errorsheet.setTjtype(rs.getString(5));
				errorsheet.setTjmsg(rs.getString(6));
				return errorsheet;
			}
		});

		pb.setBeanList(errorsheetList);
		return pb;
	}
}
